﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using Simple.Services.System.QHSEUser.Models;
using Simple.Services.System.QHSEViolationCategory.Models;

namespace Simple.Services;

public class ViolationTypeAnalysisService
{
    private readonly SimpleDbContext _context;
    private readonly QHSEXyViolationTypeService _violationTypeService;
    private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEViolationCategoryService _violationCategoryService;
    public ViolationTypeAnalysisService(SimpleDbContext context, QHSEXyViolationTypeService violationTypeService, QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEDictionaryService dictionaryService, QHSEViolationCategoryService violationCategoryService)
    {
        _context = context;
        _violationTypeService = violationTypeService;
        _keyWorkSpecialtyService = keyWorkSpecialtyService;
        _dictionaryService = dictionaryService;
        _violationCategoryService = violationCategoryService;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<List<ViolationAnalysisResult>> ViolationTypeAnalysisAsync(ViolationAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            List<QHSEXyViolationTypeModel> violationType = await _violationTypeService.GetAsync();
            if (violationType != null)
            {
                foreach (var type in violationType)
                {
                    var value = await query.Where(u => type.ViolationType.Contains(u.ViolationType)).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = type.ViolationType;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<CompareViolationAnalysisResult> ViolationTypeCategoryAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes =await _violationTypeService.GetAsync();
            var types = await _dictionaryService.GetDictionaryAsync("1014");
            List<Object> vio = new List<Object> { "通用/专业" };
            vio.AddRange(types.Select(age => age.Item));
            result.Item.Add(vio);

            foreach (var violationType in violationTypes)
            {
                List<Object> violation = new List<Object> { violationType.ViolationType };
                var violationCounts = await query
                    .Where(u => u.ViolationType == violationType.ViolationType)
                    .GroupBy(u => u.ViolationTypeCategory)
                    .Select(g => new { ViolationTypeCategory = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var type in types)
                {
                    if(type.Code == "10140003")
                    {
                        type.Item = "不区分+其他";
                    }
                    var value = violationCounts.FirstOrDefault(v => type.Item.Contains(v.ViolationTypeCategory))?.Count ?? 0;
                    if (type.Code == "10140003")
                    {
                        type.Item = "其他";
                    }
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }
        return result;
    }
    public async Task<CompareViolationAnalysisResult> WorkYearsAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes = await _violationTypeService.GetAsync();
            var workYears = await _dictionaryService.GetDictionaryAsync("1003");

            List<Object> vio = new List<Object> { "各工龄段违章性质" };
            vio.AddRange(workYears.Select(work => work.Item));
            result.Item.Add(vio);

            foreach (var violationType in violationTypes)
            {
                List<Object> violation = new List<Object> { violationType.ViolationType };
                var violationCounts = await query
                    .Where(u => u.ViolationType == violationType.ViolationType)
                    .GroupBy(u => u.WorkYears)
                    .Select(g => new { WorkYears = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var workYear in workYears)
                {
                    var newitem = "";
                    var value = violationCounts.FirstOrDefault(u => workYear.Item == u.WorkYears)?.Count ?? 0;
                    if (workYear.Item == "1年及以下")
                    {
                        value += violationCounts.FirstOrDefault(u => "1年以下".Contains(u.WorkYears))?.Count ?? 0;
                    }
                    else if (workYear.Item == "2-3年")
                    {
                        value += violationCounts.FirstOrDefault(u => "1-5年".Contains(u.WorkYears))?.Count ?? 0;
                    }
                    else if (workYear.Item == "5年以上")
                    {
                        value += violationCounts.FirstOrDefault(u => "5-10年+10-15年+15年以上".Contains(u.WorkYears))?.Count ?? 0;
                    }
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> WorkSpecialityAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes = await _violationTypeService.GetAsync();
            var workSpecialitys = await _keyWorkSpecialtyService.GetAsync();

            List<Object> vio = new List<Object> { "各专业违章性质" };
            vio.AddRange(workSpecialitys.Select(work => work.Specialty));
            result.Item.Add(vio);

            foreach (var violationType in violationTypes)
            {
                List<Object> violation = new List<Object> { violationType.ViolationType };
                var violationCounts = await query
                    .Where(u => u.ViolationType == violationType.ViolationType)
                    .GroupBy(u => u.WorkSpecialty)
                    .Select(g => new { WorkSpecialty = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var workSpeciality in workSpecialitys)
                {
                    var value = violationCounts.FirstOrDefault(v => v.WorkSpecialty == workSpeciality.Specialty)?.Count ?? 0;
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> AgeAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes = await _violationTypeService.GetAsync();
            var ages = await _dictionaryService.GetDictionaryAsync("1002");

            List<Object> vio = new List<Object> { "各年龄段违章性质" };
            vio.AddRange(ages.Select(age => age.Item));
            result.Item.Add(vio);

            foreach (var violationType in violationTypes)
            {
                List<Object> violation = new List<Object> { violationType.ViolationType };
                var violationCounts = await query
                    .Where(u => u.ViolationType == violationType.ViolationType)
                    .GroupBy(u => u.Age)
                    .Select(g => new { Age = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var age in ages)
                {
                    var value = violationCounts.FirstOrDefault(v => v.Age == age.Item)?.Count ?? 0;
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> EmployeeTypeAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes = await _violationTypeService.GetAsync();
            var employeeTypes = await _dictionaryService.GetDictionaryAsync("1004");

            List<Object> vio = new List<Object> { "用工性质违章性质" };
            vio.AddRange(employeeTypes.Select(type => type.Item));
            result.Item.Add(vio);

            foreach (var violationType in violationTypes)
            {
                List<Object> violation = new List<Object> { violationType.ViolationType };
                var violationCounts = await query
                    .Where(u => u.ViolationType == violationType.ViolationType)
                    .GroupBy(u => u.EmployeeType)
                    .Select(g => new { EmployeeType = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var employeeType in employeeTypes)
                {
                    var value = violationCounts.FirstOrDefault(v => v.EmployeeType == employeeType.Item)?.Count ?? 0;
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> ViolationCategoryAnalysisAsync(ViolationAnalysisInput input, string name)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            query = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes = await _violationTypeService.GetAsync();
            var violationCategorys = await _violationCategoryService.GetSon(name);
            if(name == "不安全因素")
            {
                name = "违章类别";
            }
            var vio = new List<object> { name };
            vio.AddRange(violationCategorys);
            result.Item.Add(vio);

            foreach (var violationType in violationTypes)
            {
                var violation = new List<object> { violationType.ViolationType };

                var violationCounts = await GetViolationCountsAsync(query, violationType.ViolationType, violationCategorys);
                var violationCountObjects = violationCounts.Cast<object>().ToList(); // 将整数转换为对象列表
                violation.AddRange(violationCountObjects);
                result.Item.Add(violation);
            }

        }

        return result;
    }

    private async Task<List<int>> GetViolationCountsAsync(IQueryable<QHSESysXyViolation> query, string violationType, List<string> violationCategories)
    {
        var violationCounts = new List<int>();

        foreach (var violationCategory in violationCategories)
        {
            var children = await _violationCategoryService.GetChildren(violationCategory);
            var count = await query
                .Where(v => v.ViolationType == violationType && children.Contains(v.ViolationCategory))
                .CountAsync();
            violationCounts.Add(count);
        }

        return violationCounts;
    }

}
